package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedList;

import ModelLayer.*;

public class DBActivityBooking implements IFDBActivityBooking {
	private Connection con;

	public DBActivityBooking() {
		con = DBConnection.getInstance().getDBcon();
	}

	@Override
	public ArrayList<ActivityBooking> getAllActivityBookings() {
		return new ArrayList<ActivityBooking>(miscWhere(""));
	}

	@Override
	public ActivityBooking getActivityBooking(long ID) {
		String wClause = "  ID = '" + ID + "'";
		return singleWhere(wClause);
	}

	@Override
	public int insertActivityBooking(ActivityBooking booking) {
		int rc = -1;
		String query = "INSERT INTO activityBooking(ID, creationDate, startDate, activityTypeID, facilityID, instructorID) VALUES('"
				+ booking.getID()
				+ "','"
				+ booking.getCreationTime().getTime()
				+ "','"
				+ booking.getStartHour().getTime()
				+ "','"
				+ booking.getActivity().getID()
				+ "','"
				+ booking.getFacility().getID()
				+ "','"
				+ booking.getInstructor().getID() + "')";
		System.out.println("insert : " + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
			con.commit();
		} catch (Exception e) {
			System.out
					.println("[ERROR] DBLayer.ActivityBooking - New entry not inserted: "
							+ e.getMessage());
		}
		return rc;
	}

	@Override
	public int updateActivityBooking(long ID, ActivityBooking booking) {
		int rc = -1;
		String query = "UPDATE activityBooking SET " + "creationDate = '"
				+ booking.getCreationTime().getTime() + "'," + "startDate = '"
				+ booking.getStartHour().getTime() + "',"
				+ "activityTypeID = '" + booking.getActivity().getID() + "',"
				+ "facilityID = '" + booking.getFacility().getID() + "',"
				+ "instructorID = '" + booking.getInstructor().getID() + "'"
				+ " WHERE ID = '" + ID + "'";
		System.out.println("Update query:" + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception e) {
			System.out.println("[ERROR] DBLayer.DBActivityBooking - Entry #"
					+ ID + " was not updated. \n" + e.getMessage());
		}
		return rc;
	}

	@Override
	public int removeActivityBooking(long ID) {
		int rc = -1;
		String query = "DELETE FROM activityBooking WHERE ID = '" + ID + "'";
		System.out.println(query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception e) {
			System.out.println("[ERROR] DBLayer.DBActivityBooking - Entry #"
					+ ID + " was not deleted. \n" + e.getMessage());
		}
		return rc;
	}

	private LinkedList<ActivityBooking> miscWhere(String wClause) {
		ResultSet results;
		LinkedList<ActivityBooking> list = new LinkedList<ActivityBooking>();
		String query = buildQuery(wClause);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			while (results.next()) {
				ActivityBooking ab = new ActivityBooking();
				ab = buildActivityBooking(results);
				list.add(ab);
			}
			stmt.close();
		} catch (Exception e) {
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}

	private ActivityBooking singleWhere(String wClause) {
		ResultSet results;
		ActivityBooking ab = new ActivityBooking();
		String query = buildQuery(wClause);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if (results.next()) {
				ab = buildActivityBooking(results);
				stmt.close();
			} else {
				ab = null;
			}
		} catch (Exception e) {
			System.out.println("Query exception: " + e);
		}
		return ab;
	}

	private ActivityBooking buildActivityBooking(ResultSet results) {
		ActivityBooking ab = new ActivityBooking();
		try {
			ab.setID(results.getLong("ID"));
			ab.setCreationTime(new Date(results.getLong("creationDate")));
			ab.setStartHour(new Date(results.getLong("startDate")));
			ab.setActivity(new ActivityType(results.getLong("activityTypeID")));
			ab.setFacility(new Facility(results.getLong("facilityID")));
			ab.setInstructor(new Instructor(results.getInt("instructorID")));
		} catch (Exception e) {
			System.out
					.println("[ERROR] DBLayer.DBActivityBooking - error in building object");
		}
		return ab;
	}

	private String buildQuery(String wClause) {
		String query = "SELECT ID, creationDate, startDate, activityTypeID, facilityID, instructorID  FROM activityBooking";

		if (wClause.length() > 0)
			query = query + " WHERE " + wClause;

		return query;
	}
}
